﻿Imports System.Data.SqlClient
Public Class AgendamentoDAL

#Region "Incluir"
    Public Function Inserir(codigo As Integer, data As Date, hrinicial As DateTime, obs As String, codplaca As Integer, codstatus As Integer) As Integer
        Dim cmd = New SqlCommand
        Try

            cmd.CommandText = "insert  into Vendas.Ofi_Agendamento (age_data,age_horainicio,age_obs,pla_codigo,sta_codigo) values (@dia,@hrinicial,@obs,@codplaca,@codstatus)"
            'cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.Date)).Value = Format(data, "yyyy-MM-dd")
            cmd.Parameters.Add(New SqlParameter("@hrinicial", SqlDbType.Time)).Value = Format(hrinicial, "HH:MM")
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = obs
            cmd.Parameters.Add(New SqlParameter("@codplaca", SqlDbType.Int)).Value = codplaca
            cmd.Parameters.Add(New SqlParameter("@codstatus", SqlDbType.Int)).Value = codstatus
            codigo = objconexao.executacmdAI(cmd, "Ofi_Agendamento")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Alterar"
    Public Function Alterar(codigo As Integer, data As Date, hrinicial As DateTime, obs As String, codplaca As Integer, codstatus As Integer) As Integer
        Dim cmd = New SqlCommand
        Try

            cmd.CommandText = "update Vendas.Ofi_Agendamento set age_data=@dia,age_horainicio=@hrinicial,age_obs=@obs,pla_codigo=@codplaca,sta_codigo=@codstatus where age_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.Date)).Value = Format(data, "yyyy-MM-dd")
            cmd.Parameters.Add(New SqlParameter("@hrinicial", SqlDbType.Time)).Value = Format(hrinicial, "HH:MM")
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = obs
            cmd.Parameters.Add(New SqlParameter("@codplaca", SqlDbType.Int)).Value = codplaca
            cmd.Parameters.Add(New SqlParameter("@codstatus", SqlDbType.Int)).Value = codstatus
            objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_Agendamento where age_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_Agendamento where cli_nome COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_Agendamento where pla_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 4
                    cmd.CommandText = "select * from vw_Agendamento where sta_descricao = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case Else
                    cmd.CommandText = "select * from vw_Agendamento where age_data= @descricao order by age_data"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Date)).Value = Descricao
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

    Function locadata(ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Filtro datas
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand  'Format(Descricao, "yyyy/MM/dd")
            cmd.CommandText = "select * from vw_Agendamento where age_data BETWEEN @dtini AND @dtfim"
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.Date)).Value = Format(dtini, "yyyy/MM/dd")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.Date)).Value = Format(dtfim, "yyyy/MM/dd")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Excluir"
    Function Excluir(ByVal Codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update Vendas.Ofi_Agendamento set sta_codigo=3 where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            Codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return Codigo
    End Function
#End Region

#Region "Valida exclusão"
    Public Function ValidaExcluir(ByVal Codigo As Integer, Status As Integer) As Boolean
        Dim cmd = New SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            If Status <> 1 Then
                cmd.CommandText = "select*from Vendas.Ofi_Agendamento a inner join Vendas.Ofi c on p.cli_codigo=c.cli_codigo where p.sta_codigo <> 3 and c.cli_codigo=@codigo and c.sta_codigo <> 3 "
                cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
                tab = objconexao.executaConsulta(cmd)
                If (tab.Rows.Count > 0) Then
                    ret = False
                End If
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region

#Region "Verifica Agendamento"
    Function VerificaAgendamento(Codigo As Integer, ByVal Data As Date, HrInicio As DateTime, HrFinal As DateTime) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_Agendamento WHERE age_data = @data and age_horainicio BETWEEN @hrinicio and @hrfinal"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.Date)).Value = Format(Data, "yyyy-MM-dd")
            cmd.Parameters.Add(New SqlParameter("@hrinicio", SqlDbType.Time)).Value = Format(HrInicio, "HH:MM")
            cmd.Parameters.Add(New SqlParameter("@hrfinal", SqlDbType.Time)).Value = Format(HrFinal, "HH:MM")
            tab = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

End Class
